package com.king.student.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.king.student.entity.Student;
import com.king.student.sql.SqlUtility;

public class StudentDao {

	//添加一个学生
	public static boolean addStudent(Student student){
		try {
			String sql = "insert into student(studentID,studentName,studentSex,studentBirthday,classID)"
					+ " values(?,?,?,?,?)";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, student.getStudentID());
			ps.setString(2, student.getStudentName());
			ps.setInt(3, student.getStudentSex());
			ps.setString(4, student.getStudentBirthday());
			ps.setString(5, student.getClassID());
			ps.execute();
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	
	//删除一个学生
	public static boolean delStudent(String id){
		try {
			String sql = "delete from student where studentID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, id);
			ps.execute();
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	
	//更新学生信息
	public static boolean updateStudent(Student student){
		try {
			String sql = "update student set studentName=?,studentSex=?,studentBirthday=?,classID=? where studentID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, student.getStudentName());
			ps.setInt(2, student.getStudentSex());
			ps.setString(3, student.getStudentBirthday());
			ps.setString(4, student.getClassID());
			ps.setString(5, student.getStudentID());
			ps.execute();
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	
	//根据学号查询学生
	public static Student getStudentByStudentID(String studentID){
		try {
			String sql = "select * from student where studentID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, studentID);
			ResultSet rs = ps.executeQuery();
			rs.next();
			Student student = new Student();
			student.setStudentID(rs.getString("studentID"));
			student.setStudentName(rs.getString("studentName"));
			student.setStudentSex(rs.getInt("studentSex"));
			student.setStudentBirthday(rs.getString("studentBirthday"));
			student.setClassID(rs.getString("classID"));
			return student;
		} catch (SQLException e) {
		}
		return null;
	}
	
	//模糊查询
	public static List<Student> getStudentByFuzzyQuery(String key){
		try {
			String sql = "select * from student where "
					+ "studentID like binary '%"+key+"%' or "
					+ "studentName like binary '%"+key+"%' or "
					+ "studentSex like binary '%"+key+"%' or "
					+ "studentBirthday like binary '%"+key+"%' or "
					+ "classID like binary '%"+key+"%'";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			List<Student> list = new ArrayList<>();
			while(rs.next()){
				Student student = new Student();
				student.setStudentID(rs.getString("studentID"));
				student.setStudentName(rs.getString("studentName"));
				student.setStudentSex(rs.getInt("studentSex"));
				student.setStudentBirthday(rs.getString("studentBirthday"));
				student.setClassID(rs.getString("classID"));
				list.add(student);
			}
			return list;
		} catch (SQLException e) {
		}
		return Collections.emptyList();
	}
	
	//根据班级查找学生
	public static List<Student> getStudentByClassID(String classID){
		try {
			String sql = "select * from student where classID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, classID);
			ResultSet rs = ps.executeQuery();
			List<Student> list = new ArrayList<>();
			while(rs.next()){
				Student student = new Student();
				student.setStudentID(rs.getString("studentID"));
				student.setStudentName(rs.getString("studentName"));
				student.setStudentSex(rs.getInt("studentSex"));
				student.setStudentBirthday(rs.getString("studentBirthday"));
				student.setClassID(rs.getString("classID"));
				list.add(student);
			}
			return list;
		} catch (SQLException e) {
		}
		return Collections.emptyList();
	}
	
}
